package com.regulareport.common.utils.poi;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFColor;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.node.ArrayNode;
import com.fasterxml.jackson.databind.node.ObjectNode;

import java.io.*;
import java.util.*;

public class ExcelToJsonConverterXSSF{

    private final ObjectMapper objectMapper = new ObjectMapper();

    /**
     * 转换Excel为包含合并单元格和样式的JSON
     */
    public String convertExcelWithStyles(String filePath) throws Exception {
        try (FileInputStream file = new FileInputStream(filePath);
             Workbook workbook = new XSSFWorkbook(file)) {

            ObjectNode result = objectMapper.createObjectNode();
            ArrayNode worksheets = objectMapper.createArrayNode();

            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                Sheet sheet = workbook.getSheetAt(i);
                ObjectNode sheetData = convertSheetWithFullStyles(sheet);
                worksheets.add(sheetData);
            }

            result.set("worksheets", worksheets);
            return objectMapper.writerWithDefaultPrettyPrinter().writeValueAsString(result);
        }
    }

    /**
     * 转换单个工作表（完整样式支持）
     */
    private ObjectNode convertSheetWithFullStyles(Sheet sheet) {
        ObjectNode sheetNode = objectMapper.createObjectNode();
        sheetNode.put("name", sheet.getSheetName());

        // 处理合并单元格
        List<CellRangeAddress> mergedRegions = sheet.getMergedRegions();
        ArrayNode mergeCellsArray = objectMapper.createArrayNode();

        for (CellRangeAddress mergedRegion : mergedRegions) {
            ObjectNode mergeNode = objectMapper.createObjectNode();
            mergeNode.put("range", mergedRegion.formatAsString());
            mergeNode.put("firstRow", mergedRegion.getFirstRow());
            mergeNode.put("lastRow", mergedRegion.getLastRow());
            mergeNode.put("firstColumn", mergedRegion.getFirstColumn());
            mergeNode.put("lastColumn", mergedRegion.getLastColumn());

            // 获取主单元格的值和样式
            Row firstRow = sheet.getRow(mergedRegion.getFirstRow());
            if (firstRow != null) {
                Cell firstCell = firstRow.getCell(mergedRegion.getFirstColumn());
                if (firstCell != null) {
                    mergeNode.put("value", getCellValueAsString(firstCell));
                    mergeNode.set("style", extractCellStyle(firstCell));
                }
            }

            mergeCellsArray.add(mergeNode);
        }
        sheetNode.set("mergeCells", mergeCellsArray);

        // 创建合并单元格映射，用于后续检查
        Map<String, CellRangeAddress> mergeMap = new HashMap<>();
        for (CellRangeAddress mergedRegion : mergedRegions) {
            for (int row = mergedRegion.getFirstRow(); row <= mergedRegion.getLastRow(); row++) {
                for (int col = mergedRegion.getFirstColumn(); col <= mergedRegion.getLastColumn(); col++) {
                    String key = row + "," + col;
                    mergeMap.put(key, mergedRegion);
                }
            }
        }

        // 处理所有单元格数据
        ArrayNode rowsArray = objectMapper.createArrayNode();
        int lastRowNum = sheet.getLastRowNum();

        for (int rowNum = 0; rowNum <= lastRowNum; rowNum++) {
            Row row = sheet.getRow(rowNum);
            if (row == null) {
                // 处理空行
                ObjectNode emptyRowNode = objectMapper.createObjectNode();
                emptyRowNode.put("rowIndex", rowNum);
                emptyRowNode.put("isEmpty", true);
                rowsArray.add(emptyRowNode);
                continue;
            }

            ObjectNode rowNode = objectMapper.createObjectNode();
            rowNode.put("rowIndex", rowNum);
            rowNode.put("isEmpty", false);

            ArrayNode cellsArray = objectMapper.createArrayNode();
            short lastCellNum = row.getLastCellNum();

            for (int colNum = 0; colNum < lastCellNum; colNum++) {
                Cell cell = row.getCell(colNum);
                String cellKey = rowNum + "," + colNum;

                ObjectNode cellNode = objectMapper.createObjectNode();
                cellNode.put("columnIndex", colNum);
                cellNode.put("columnName", getColumnName(colNum));

                // 检查是否是合并单元格的一部分
                if (mergeMap.containsKey(cellKey)) {
                    CellRangeAddress mergedRegion = mergeMap.get(cellKey);
                    cellNode.put("isMerged", true);
                    cellNode.put("isMaster",
                            mergedRegion.getFirstRow() == rowNum &&
                                    mergedRegion.getFirstColumn() == colNum);
                    cellNode.put("mergeRange", mergedRegion.formatAsString());

                    // 如果是主单元格，保存值和样式
                    if (cellNode.get("isMaster").asBoolean() && cell != null) {
                        cellNode.put("value", getCellValueAsString(cell));
                        cellNode.set("style", extractCellStyle(cell));
                    } else {
                        // 子单元格继承主单元格的值和样式
                        Row masterRow = sheet.getRow(mergedRegion.getFirstRow());
                        Cell masterCell = masterRow.getCell(mergedRegion.getFirstColumn());
                        if (masterCell != null) {
                            cellNode.put("value", getCellValueAsString(masterCell));
                            cellNode.set("style", extractCellStyle(masterCell));
                        }
                    }
                } else if (cell != null) {
                    // 普通单元格
                    cellNode.put("isMerged", false);
                    cellNode.put("isMaster", false);
                    cellNode.put("value", getCellValueAsString(cell));
                    cellNode.set("style", extractCellStyle(cell));
                } else {
                    // 空单元格
                    cellNode.put("isMerged", false);
                    cellNode.put("isMaster", false);
                    cellNode.put("value", "");
                    cellNode.set("style", objectMapper.createObjectNode());
                }

                cellsArray.add(cellNode);
            }

            rowNode.set("cells", cellsArray);
            rowsArray.add(rowNode);
        }

        sheetNode.set("rows", rowsArray);
        return sheetNode;
    }

    /**
     * 提取单元格样式信息
     */
    private ObjectNode extractCellStyle(Cell cell) {
        ObjectNode styleNode = objectMapper.createObjectNode();
        if (cell == null) return styleNode;

        CellStyle cellStyle = cell.getCellStyle();
        Workbook workbook = cell.getSheet().getWorkbook();

        // 背景颜色
        try {
            Color bgColor = cellStyle.getFillForegroundColorColor();
            if (bgColor instanceof XSSFColor) {
                XSSFColor xssfColor = (XSSFColor) bgColor;
                if (xssfColor.getARGBHex() != null) {
                    styleNode.put("backgroundColor", xssfColor.getARGBHex());
                }
            }
        } catch (Exception e) {
            // 忽略颜色提取错误
        }

        // 字体样式
        try {
//            XSSFFont font = ((XSSFCellStyle) cell.getCellStyle()).getFont();
            Font font = workbook.getFontAt(cellStyle.getFontIndex());
            if (font != null) {
                ObjectNode fontNode = objectMapper.createObjectNode();
                fontNode.put("name", font.getFontName());
                fontNode.put("size", font.getFontHeightInPoints());
                fontNode.put("bold", font.getBold());
                fontNode.put("italic", font.getItalic());
                fontNode.put("underline", font.getUnderline());

                // 字体颜色
//                short fontColor = font.getColor();
//                if (fontColor instanceof XSSFColor) {
//                    XSSFColor xssfFontColor = (XSSFColor) fontColor;
//                    if (xssfFontColor.getARGBHex() != null) {
//                        fontNode.put("color", xssfFontColor.getARGBHex());
//                    }
//                }

                styleNode.set("font", fontNode);
            }
        } catch (Exception e) {
            // 忽略字体提取错误
        }

        // 边框样式
        try {
            ObjectNode borderNode = objectMapper.createObjectNode();
            borderNode.put("top", getBorderStyle(cellStyle.getBorderTop()));
            borderNode.put("bottom", getBorderStyle(cellStyle.getBorderBottom()));
            borderNode.put("left", getBorderStyle(cellStyle.getBorderLeft()));
            borderNode.put("right", getBorderStyle(cellStyle.getBorderRight()));
            styleNode.set("border", borderNode);
        } catch (Exception e) {
            // 忽略边框提取错误
        }

        // 对齐方式
        try {
            styleNode.put("horizontalAlignment", cellStyle.getAlignment().toString());
            styleNode.put("verticalAlignment", cellStyle.getVerticalAlignment().toString());
        } catch (Exception e) {
            // 忽略对齐方式提取错误
        }

        return styleNode;
    }

    /**
     * 获取边框样式名称
     */
    private String getBorderStyle(BorderStyle borderStyle) {
        if (borderStyle == null) return "NONE";
        return borderStyle.toString();
    }

    /**
     * 获取单元格值的字符串表示
     */
    private String getCellValueAsString(Cell cell) {
        if (cell == null) return "";

        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    // 避免科学计数法
                    double num = cell.getNumericCellValue();
                    if (num == (long) num) {
                        return String.valueOf((long) num);
                    } else {
                        return String.valueOf(num);
                    }
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                try {
                    return String.valueOf(cell.getNumericCellValue());
                } catch (Exception e) {
                    try {
                        return cell.getStringCellValue();
                    } catch (Exception ex) {
                        return cell.getCellFormula();
                    }
                }
            case BLANK:
                return "";
            default:
                return "";
        }
    }

    /**
     * 获取列名（A, B, C, ...）
     */
    private String getColumnName(int columnIndex) {
        StringBuilder columnName = new StringBuilder();
        while (columnIndex >= 0) {
            int remainder = columnIndex % 26;
            columnName.insert(0, (char) ('A' + remainder));
            columnIndex = (columnIndex / 26) - 1;
        }
        return columnName.toString();
    }

    /**
     * 保存JSON到文件
     */
    public void saveJsonToFile(String jsonData, String outputPath) throws IOException {
        try (FileWriter writer = new FileWriter(outputPath)) {
            writer.write(jsonData);
        }
    }

    /**
     * 使用示例
     */
    public static void main(String[] args) {
        try {
            ExcelToJsonConverterXSSF converter = new ExcelToJsonConverterXSSF();

            // 转换Excel为包含样式的JSON
            String jsonData = converter.convertExcelWithStyles("D:\\temp\\document\\1104\\GF0401-231.xls");

            // 保存到文件
            converter.saveJsonToFile(jsonData, "output_with_styles.json");

            System.out.println("转换完成！文件已保存为: output_with_styles.json");

            // 打印部分结果预览
            System.out.println("预览前500字符:");
            System.out.println(jsonData.substring(0, Math.min(500, jsonData.length())) + "...");

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}